﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DFamousRegionPaper
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into FamousRegionPaper (FamousRegionPaperId,FamousRegionId,RegionCategoryId,PaperId,DateCreated) values ( @FamousRegionPaperId,@FamousRegionId,@RegionCategoryId,@PaperId,@DateCreated)";

        private const string SQL_UPDATE = "update FamousRegionPaper set FamousRegionId=@FamousRegionId,RegionCategoryId=@RegionCategoryId,PaperId=@PaperId,DateCreated=@DateCreated where FamousRegionPaperId=@FamousRegionPaperId";

        private const string SQL_SELECT_ID = "select  FamousRegionPaperId,FamousRegionId,RegionCategoryId,PaperId,DateCreated from FamousRegionPaper where FamousRegionPaperId=@FamousRegionPaperId";

        private const string SQL_SELECT_REGIONID = "select  FamousRegionPaperId,FamousRegionId,RegionCategoryId,PaperId,DateCreated from FamousRegionPaper where FamousRegionId=@FamousRegionId";

        private const string SQL_SELECT_FAMOUS_REGIONID_BY_IDS = "select f.FamousRegionPaperId,f.FamousRegionId,f.RegionCategoryId,f.PaperId,f.DateCreated from FamousRegionPaper f, Paper p where FamousRegionId = @FamousRegionId and RegionCategoryId=@RegionCategoryId and f.PaperId = p.PaperId and p.status = 1 order by f.DateCreated desc";

        #endregion
        
        

        #region 公共接口
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="famousRegionPaperInfo"></param>
        /// <returns></returns>
        public bool Insert(FamousRegionPaperInfo famousRegionPaperInfo)
        {
            if (famousRegionPaperInfo == null)
                throw new ArgumentNullException("paperInfo");

            SqlParameter[] parms = GetInsertParms(famousRegionPaperInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="famousRegionPaperInfo"></param>
        /// <returns></returns>
        public bool Update(FamousRegionPaperInfo famousRegionPaperInfo)
        {
            if (famousRegionPaperInfo == null || famousRegionPaperInfo.FamousRegionId.ToString().Length != 18)
                throw new ArgumentNullException("famousRegionInfo");

            SqlParameter[] parms = GetInsertParms(famousRegionPaperInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 根据专区Id获取专区文章
        /// </summary>
        /// <param name="famousRegionId"></param>
        /// <returns></returns>
        public List<FamousRegionPaperInfo> GetFamousRegionPagers(long famousRegionId)
        {
            List<FamousRegionPaperInfo> famousRegionPapers = new List<FamousRegionPaperInfo>();
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionId",SqlDbType.BigInt)
                };
            parms[0].Value = famousRegionId;
            FamousRegionPaperInfo current = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_REGIONID, parms))
            {
                while (reader.Read())
                {
                    current = GetInstanceByDataReader(reader);

                    if (current != null)
                        famousRegionPapers.Add(current);

                }
            }
            return famousRegionPapers;
        }

        /// <summary>
        /// 根据Id获取名人专区文章
        /// </summary>
        /// <param name="famousRegionPagerId"></param>
        /// <returns></returns>
        public FamousRegionPaperInfo GetFamousRegionPager(long famousRegionPagerId)
        {
            FamousRegionPaperInfo famousRegionPagerInfo = new FamousRegionPaperInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionPaperId",SqlDbType.BigInt)
                };
            parms[0].Value = famousRegionPagerId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    famousRegionPagerInfo = GetInstanceByDataReader(reader);

                }
            }
            return famousRegionPagerInfo;
        }

        public List<FamousRegionPaperInfo> GetFamousRegionPagersByIds(long FamousRegionId, long CategoryId)
        {
            List<FamousRegionPaperInfo> famousRegionPapers = new List<FamousRegionPaperInfo>();
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionId",SqlDbType.BigInt),
                    new SqlParameter("@RegionCategoryId",SqlDbType.BigInt)
                };
            parms[0].Value = FamousRegionId;
            parms[1].Value = CategoryId;
            FamousRegionPaperInfo current = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_FAMOUS_REGIONID_BY_IDS, parms))
            {
                while (reader.Read())
                {
                    current = GetInstanceByDataReader(reader);

                    if (current != null)
                        famousRegionPapers.Add(current);

                }
            }
            return famousRegionPapers;
        }
        #endregion

        #region 私有接口

        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(FamousRegionPaperInfo famousRegionPaperInfo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@FamousRegionPaperId", SqlDbType.BigInt,8),
					new SqlParameter("@FamousRegionId", SqlDbType.BigInt,8),
					new SqlParameter("@RegionCategoryId", SqlDbType.BigInt,8),
					new SqlParameter("@PaperId", SqlDbType.BigInt,8),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            return SetInsertParms(parameters, famousRegionPaperInfo);
        }

        private SqlParameter[] SetInsertParms(SqlParameter[] parameters, FamousRegionPaperInfo model)
        {
            parameters[0].Value = model.FamousRegionPaperId;
            parameters[1].Value = model.FamousRegionId;
            parameters[2].Value = model.RegionCategoryId;
            parameters[3].Value = model.PaperId;
            parameters[4].Value = model.DateCreated;

            return parameters;
        }
        private FamousRegionPaperInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            FamousRegionPaperInfo current = new FamousRegionPaperInfo();
            //FamousRegionPaperId,FamousRegionId,RegionCategoryId,PaperId,DateCreated

            current.FamousRegionPaperId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.FamousRegionId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.RegionCategoryId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
            current.PaperId = reader.IsDBNull(3) ? 0 : reader.GetInt64(3);
            current.DateCreated = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);



            return current;

        }
        #endregion
    }
}
